package com.xxl.job.executor.service.impl;

import com.xxl.job.core.log.XxlJobLogger;
import com.xxl.job.executor.common.util.DBUtils;
import com.xxl.job.executor.common.util.SpringContextHolder;
import com.xxl.job.executor.service.IMonitorInfoReqService;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

@Service("monitorInfoReqService")
public class MonitorInfoReqService implements IMonitorInfoReqService {
    Connection connPre = null;

    @Override
    public void transfer() throws Exception {
        XxlJobLogger.log("=====开始执行业务操作===");

        try {
            DataSource dataSourcePre = SpringContextHolder.getBean("dataSourcePre");
            connPre = DataSourceUtils.getConnection(dataSourcePre);
            connPre.setAutoCommit(false);
            //跨省出口ETC交易量
            syncETCOutSaShen(null,null, connPre);
            //同步本省ect出口数据量
            syncETCOutbensheng(null,null, connPre);
            //同步跨省mtc出口数据量
            syncMTCOutKuasheng(null,null, connPre);
            // 本省出口其它交易量
            syncMTCOutbensheng(null,null, connPre);
            //入口ETC交易量
            syncInETC(null,null, connPre);
            //入口其它交易量
            syncOtherMTC(null,null, connPre);
            //-门架通行链量
            syncGranyTxNum(null,null, connPre);
            //门架etc交易量
            sysnETCGrantryNum(null,null, connPre);
            //门架cpc交易量
            sysnCPCGrantryNum(null,null, connPre);
            
            connPre.commit();

            XxlJobLogger.log("=====完成执行业务操作===");
        }catch(Exception e){
            if (connPre != null) {
                try {
                    connPre.rollback();
                    XxlJobLogger.log("回滚成功.");
                }catch (Exception e1){
                    e1.printStackTrace();
                }
            }
            XxlJobLogger.log("抛出异常："+e.getMessage());
            throw e;
        }finally{
            if (connPre != null) {
                try {
                    connPre.setAutoCommit(true);
                    connPre.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
    }
    
    /**
     * 跨省出口ETC交易量
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException
     */
    private void syncETCOutSaShen(String startTime,String endTime,Connection conn) throws SQLException { 
    	String sql = " merge into TB_STATIS_MONITOR_SIDE a\r\n" + 
    			"  using (\r\n" + 
    			"  select t.TRADEDATE as STATIS_DATE,'1' as STATIS_TYPE,'2' as  STATIS_KIND,DIFFETCOUTRECV as RECV_NUM,DIFFETCOUTUPLOAD as UPLOAD_NUM,\r\n" + 
    			"  DIFFETCOUTACCOUNT as ACCOUNT_NUM,DIFFETCOUTSPLIT as SPLIT_NUM\r\n" + 
    			"  from TB_CHARGE_MONITOR_INTF_RECV t   \r\n" + 
    			//"  where to_date(t.TRADEDATE,'yyyy-mm-dd')>=to_date('"+startTime+"','yyyy-mm-dd') " +
    		    //"  to_date(t.TRADEDATE,'yyyy-mm-dd')<to_date('"+endTime+"','yyyy-mm-dd') \r\n" + 
    			"  ) b on (a.STATIS_DATE=b.STATIS_DATE and a.STATIS_TYPE=b.STATIS_TYPE and a.STATIS_KIND =b.STATIS_KIND)\r\n" + 
    			"  WHEN MATCHED THEN \r\n" + 
    			"   update set a.recv_num=b.recv_num,a.upload_num=b.upload_num,a.account_num=b.account_num,a.split_num=b.split_num,UPDATE_TIME = sysdate\r\n" + 
    			"  WHEN not MATCHED THEN   \r\n" + 
    			"  insert (STATIS_DATE,STATIS_TYPE,STATIS_KIND,RECV_NUM,UPLOAD_NUM,ACCOUNT_NUM,SPLIT_NUM,INESRT_TIME,UPDATE_TIME)\r\n" + 
    			"  values (b.STATIS_DATE,b.STATIS_TYPE,b.STATIS_KIND,b.RECV_NUM,b.UPLOAD_NUM,b.ACCOUNT_NUM,b.SPLIT_NUM,sysdate,sysdate) ";
    	Integer count = DBUtils.execute(conn, sql);
 		XxlJobLogger.log("[普通] 跨省出口ETC交易量数据(条):" + count);	
    }
   
    
    /**
     * 同步本省ect出口数据量
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException
     */
    private void syncETCOutbensheng(String startTime,String endTime,Connection conn) throws SQLException { 
    	String sql = " merge into TB_STATIS_MONITOR_SIDE a\r\n" + 
    			"  using (\r\n" + 
    			"  select t.TRADEDATE as STATIS_DATE,'1' as STATIS_TYPE,'1' as  STATIS_KIND,LOCALETCOUTRECV as RECV_NUM,\r\n" + 
    			"  LOCALETCOUTUPLOAD as UPLOAD_NUM,  LOCALETCOUTACCOUNT as ACCOUNT_NUM,LOCALETCOUTSPLIT as SPLIT_NUM\r\n" +     			
    			"  from TB_CHARGE_MONITOR_INTF_RECV t   \r\n" + 
    			//"  where to_date(t.TRADEDATE,'yyyy-mm-dd')>=to_date('"+startTime+"','yyyy-mm-dd') " +
    		   // "  to_date(t.TRADEDATE,'yyyy-mm-dd')<to_date('"+endTime+"','yyyy-mm-dd') \r\n" + 
    			"  ) b on (a.STATIS_DATE=b.STATIS_DATE and a.STATIS_TYPE=b.STATIS_TYPE and a.STATIS_KIND =b.STATIS_KIND)\r\n" + 
    			"  WHEN MATCHED THEN \r\n" + 
    			"   update set a.recv_num=b.recv_num,a.upload_num=b.upload_num,a.account_num=b.account_num,a.split_num=b.split_num,UPDATE_TIME = sysdate\r\n" + 
    			"  WHEN not MATCHED THEN   \r\n" + 
    			"  insert (STATIS_DATE,STATIS_TYPE,STATIS_KIND,RECV_NUM,UPLOAD_NUM,ACCOUNT_NUM,SPLIT_NUM,INESRT_TIME,UPDATE_TIME)\r\n" + 
    			"  values (b.STATIS_DATE,b.STATIS_TYPE,b.STATIS_KIND,b.RECV_NUM,b.UPLOAD_NUM,b.ACCOUNT_NUM,b.SPLIT_NUM,sysdate,sysdate) ";
    	Integer count = DBUtils.execute(conn, sql);
 		XxlJobLogger.log("[普通] 本省出口ETC交易量数据(条):" + count);	
    }
    
    /**
     * 同步跨省mtc出口数据量
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException
     */
    private void syncMTCOutKuasheng(String startTime,String endTime,Connection conn) throws SQLException { 
    	String sql = " merge into TB_STATIS_MONITOR_SIDE a\r\n" + 
    			"  using (\r\n" + 
    			"  select t.TRADEDATE as STATIS_DATE,'2' as STATIS_TYPE,'2' as  STATIS_KIND,DIFFMTCOUTRECV as RECV_NUM,\r\n" + 
    			"  DIFFMTCOUTUPLOAD as UPLOAD_NUM,  DIFFMTCOUTSPLIT as SPLIT_NUM\r\n" + 
    			"  from TB_CHARGE_MONITOR_INTF_RECV t   \r\n" + 
    			//"  where to_date(t.TRADEDATE,'yyyy-mm-dd')>=to_date('"+startTime+"','yyyy-mm-dd') " +
    		    //"  to_date(t.TRADEDATE,'yyyy-mm-dd')<to_date('"+endTime+"','yyyy-mm-dd') \r\n" + 
    			"  \r\n" + 
    			"  ) b on (a.STATIS_DATE=b.STATIS_DATE and a.STATIS_TYPE=b.STATIS_TYPE and a.STATIS_KIND =b.STATIS_KIND)\r\n" + 
    			"  WHEN MATCHED THEN \r\n" + 
    			"   update set a.recv_num=b.recv_num,a.upload_num=b.upload_num,a.split_num=b.split_num,UPDATE_TIME = sysdate\r\n" + 
    			"  WHEN not MATCHED THEN   \r\n" + 
    			"  insert (STATIS_DATE,STATIS_TYPE,STATIS_KIND,RECV_NUM,UPLOAD_NUM,SPLIT_NUM,INESRT_TIME,UPDATE_TIME)\r\n" + 
    			"  values (b.STATIS_DATE,b.STATIS_TYPE,b.STATIS_KIND,b.RECV_NUM,b.UPLOAD_NUM,b.SPLIT_NUM,sysdate,sysdate) ";
    	Integer count = DBUtils.execute(conn, sql);
 		XxlJobLogger.log("[普通] 跨省mtc交易量数据(条):" + count);	
    }
    
    /**
     * 本省出口其它交易量
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException
     */
    private void syncMTCOutbensheng(String startTime,String endTime,Connection conn) throws SQLException {
        String sql = " merge into TB_STATIS_MONITOR_SIDE a\r\n" + 
        		"   using (\r\n" + 
        		"  select t.TRADEDATE as STATIS_DATE,'2' as STATIS_TYPE,'1' as  STATIS_KIND,LOCALMTCOUTRECV as RECV_NUM,\r\n" + 
        		"    LOCALMTCOUTSPLIT as SPLIT_NUM\r\n" + 
        		"  from TB_CHARGE_MONITOR_INTF_RECV t   \r\n" + 
        		//"  where to_date(t.TRADEDATE,'yyyy-mm-dd')>=to_date('"+startTime+"','yyyy-mm-dd')\r\n" + 
    			//"    to_date(t.TRADEDATE,'yyyy-mm-dd')<to_date('"+endTime+"','yyyy-mm-dd')   "+        		
        		"  ) b on (a.STATIS_DATE=b.STATIS_DATE and a.STATIS_TYPE=b.STATIS_TYPE and a.STATIS_KIND =b.STATIS_KIND)\r\n" + 
        		"   WHEN MATCHED THEN \r\n" + 
        		"   update set a.recv_num=b.recv_num,a.split_num=b.split_num,UPDATE_TIME = sysdate\r\n" + 
        		"  WHEN not MATCHED THEN   \r\n" + 
        		"  insert (STATIS_DATE,STATIS_TYPE,STATIS_KIND,RECV_NUM,SPLIT_NUM,INESRT_TIME,UPDATE_TIME)\r\n" + 
        		"  values (b.STATIS_DATE,b.STATIS_TYPE,b.STATIS_KIND,b.RECV_NUM,b.SPLIT_NUM,sysdate,sysdate)  ";
        Integer count = DBUtils.execute(conn, sql);
 		XxlJobLogger.log("[普通] 本省mtc交易量数据(条):" + count);	
    }
    
    /**
     * 入口ETC交易量
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException
     */
    private void syncInETC(String startTime,String endTime,Connection conn) throws SQLException {
    	String sql = " merge into TB_STATIS_MONITOR_SIDE a \r\n" + 
    			"  using ( \r\n" + 
    			"  select t.TRADEDATE as STATIS_DATE,'4' as STATIS_TYPE,'4' as  STATIS_KIND,ETCINRECV as RECV_NUM           \r\n" + 
    			"  from TB_CHARGE_MONITOR_INTF_RECV t    \r\n" + 
    			//"  where to_date(t.TRADEDATE,'yyyy-mm-dd')>=to_date('"+startTime+"','yyyy-mm-dd')\r\n" + 
    			//"    to_date(t.TRADEDATE,'yyyy-mm-dd')<to_date('"+endTime+"','yyyy-mm-dd')               \r\n" + 
    			"  ) b on (a.STATIS_DATE=b.STATIS_DATE and a.STATIS_TYPE=b.STATIS_TYPE and a.STATIS_KIND =b.STATIS_KIND) \r\n" + 
    			"  WHEN MATCHED THEN  \r\n" + 
    			"   update set a.recv_num=b.recv_num,UPDATE_TIME = sysdate \r\n" + 
    			"  WHEN not MATCHED THEN    \r\n" + 
    			"  insert (STATIS_DATE,STATIS_TYPE,STATIS_KIND,RECV_NUM,INESRT_TIME,UPDATE_TIME) \r\n" + 
    			"  values (b.STATIS_DATE,b.STATIS_TYPE,b.STATIS_KIND,b.RECV_NUM,sysdate,sysdate) ";
    	 Integer count = DBUtils.execute(conn, sql);
  		XxlJobLogger.log("[普通] 入口ETC交易量数据(条):" + count);	
    }
    
    /**
     * 入口其它交易量
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException 
     */
    private void syncOtherMTC(String startTime,String endTime,Connection conn) throws SQLException {
    	String sql = "  merge into TB_STATIS_MONITOR_SIDE a \r\n" + 
    			"  using ( \r\n" + 
    			"  select t.TRADEDATE as STATIS_DATE,'5' as STATIS_TYPE,'5' as  STATIS_KIND,MTCINRECV as RECV_NUM           \r\n" + 
    			"  from TB_CHARGE_MONITOR_INTF_RECV t    \r\n" + 
    			//"  where to_date(t.TRADEDATE,'yyyy-mm-dd')>=to_date('"+startTime+"','yyyy-mm-dd')\r\n" + 
    			//"    to_date(t.TRADEDATE,'yyyy-mm-dd')<to_date('"+endTime+"','yyyy-mm-dd')               \r\n" + 
    			"  ) b on (a.STATIS_DATE=b.STATIS_DATE and a.STATIS_TYPE=b.STATIS_TYPE and a.STATIS_KIND =b.STATIS_KIND) \r\n" + 
    			"  WHEN MATCHED THEN  \r\n" + 
    			"   update set a.recv_num=b.recv_num,UPDATE_TIME = sysdate \r\n" + 
    			"  WHEN not MATCHED THEN    \r\n" + 
    			"  insert (STATIS_DATE,STATIS_TYPE,STATIS_KIND,RECV_NUM,INESRT_TIME,UPDATE_TIME) \r\n" + 
    			"  values (b.STATIS_DATE,b.STATIS_TYPE,b.STATIS_KIND,b.RECV_NUM,sysdate,sysdate)  ";
        Integer count = DBUtils.execute(conn, sql);
		XxlJobLogger.log("[普通] 入口ETC交易量数据(条):" + count);	
    }
    
    /**
     * -门架通行链量
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException
     */
    private void syncGranyTxNum(String startTime,String endTime,Connection conn) throws SQLException {
    	String sql = "  merge into TB_STATIS_MONITOR_SIDE a \r\n" + 
    			"  using ( \r\n" + 
    			"  select t.TRADEDATE as STATIS_DATE,'3' as STATIS_TYPE,'3' as  STATIS_KIND,GRANTRYPASSGEN as RECV_NUM,\r\n" + 
    			"  GRANTRYPASSUPLOAD as UPLOAD_NUM,GRANTRYPASSMERGE as ACCOUNT_NUM           \r\n" + 
    			"  from TB_CHARGE_MONITOR_INTF_RECV t    \r\n" + 
    			//"  where to_date(t.TRADEDATE,'yyyy-mm-dd')>=to_date('"+startTime+"','yyyy-mm-dd')\r\n" + 
    			//"   and to_date(t.TRADEDATE,'yyyy-mm-dd')<to_date('"+endTime+"','yyyy-mm-dd')               \r\n" + 
    			"  ) b on (a.STATIS_DATE=b.STATIS_DATE and a.STATIS_TYPE=b.STATIS_TYPE and a.STATIS_KIND =b.STATIS_KIND) \r\n" + 
    			"  WHEN MATCHED THEN  \r\n" + 
    			"   update set a.recv_num=b.recv_num,a.upload_num=b.UPLOAD_NUM,a.ACCOUNT_NUM=b.ACCOUNT_NUM,UPDATE_TIME = sysdate \r\n" + 
    			"  WHEN not MATCHED THEN    \r\n" + 
    			"  insert (STATIS_DATE,STATIS_TYPE,STATIS_KIND,RECV_NUM,UPLOAD_NUM,ACCOUNT_NUM,INESRT_TIME,UPDATE_TIME) \r\n" + 
    			"  values (b.STATIS_DATE,b.STATIS_TYPE,b.STATIS_KIND,b.RECV_NUM,b.UPLOAD_NUM,b.ACCOUNT_NUM,sysdate,sysdate)  ";
    	 Integer count = DBUtils.execute(conn, sql);
 		 XxlJobLogger.log("[普通] 门架通行链量数据(条):" + count);	    	
    }
    
    
    /**
     * 门架etc交易量
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException
     */
    private void sysnETCGrantryNum(String startTime,String endTime,Connection conn) throws SQLException {
    	String sql = "  merge into TB_STATIS_MONITOR_SIDE a \r\n" + 
    			"  using ( \r\n" + 
    			"  select t.TRADEDATE as STATIS_DATE,'6' as STATIS_TYPE,'6' as  STATIS_KIND,\r\n" + 
    			"   GRANTRYINETCRECV+GRANTRYOUTETCRECV+GRANTRYETCRECV as RECV_NUM       \r\n" + 
    			//"  where to_date(t.TRADEDATE,'yyyy-mm-dd')>=to_date('"+startTime+"','yyyy-mm-dd')\r\n" + 
    			//"   and to_date(t.TRADEDATE,'yyyy-mm-dd')<to_date('"+endTime+"','yyyy-mm-dd')               \r\n" + 
    			"  from TB_CHARGE_MONITOR_INTF_RECV t    \r\n" + 
    			//"  where to_date(t.TRADEDATE,'yyyy-mm-dd')>=to_date('+startTime+','yyyy-mm-dd')\r\n" + 
    			//"   and to_date(t.TRADEDATE,'yyyy-mm-dd')<to_date('+endTime+','yyyy-mm-dd')               \r\n" + 
    			"  ) b on (a.STATIS_DATE=b.STATIS_DATE and a.STATIS_TYPE=b.STATIS_TYPE and a.STATIS_KIND =b.STATIS_KIND) \r\n" + 
    			"  WHEN MATCHED THEN  \r\n" + 
    			"   update set a.recv_num=b.recv_num,UPDATE_TIME = sysdate \r\n" + 
    			"  WHEN not MATCHED THEN    \r\n" + 
    			"  insert (STATIS_DATE,STATIS_TYPE,STATIS_KIND,RECV_NUM,INESRT_TIME,UPDATE_TIME) \r\n" + 
    			"  values (b.STATIS_DATE,b.STATIS_TYPE,b.STATIS_KIND,b.RECV_NUM,sysdate,sysdate)  ";
    	 Integer count = DBUtils.execute(conn, sql);
 		 XxlJobLogger.log("[普通]  门架etc交易量数据(条):" + count);	   
    }
    
    /**
     * 门架cpc交易量
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException
     */
    private void sysnCPCGrantryNum(String startTime,String endTime,Connection conn) throws SQLException {
    	String sql ="  merge into TB_STATIS_MONITOR_SIDE a \r\n" + 
    			"  using ( \r\n" + 
    			"  select t.TRADEDATE as STATIS_DATE,'7' as STATIS_TYPE,'7' as  STATIS_KIND,\r\n" + 
    			"   GRANTRYINCPCRECV+GRANTRYOUTCPCRECV+GRANTRYCPCRECV as RECV_NUM       \r\n" + 
    			"  from TB_CHARGE_MONITOR_INTF_RECV t    \r\n" + 
    			//"  where to_date(t.TRADEDATE,'yyyy-mm-dd')>=to_date('+startTime+','yyyy-mm-dd')\r\n" + 
    			//"   and to_date(t.TRADEDATE,'yyyy-mm-dd')<to_date('+endTime+','yyyy-mm-dd')               \r\n" + 
    			"  ) b on (a.STATIS_DATE=b.STATIS_DATE and a.STATIS_TYPE=b.STATIS_TYPE and a.STATIS_KIND =b.STATIS_KIND) \r\n" + 
    			"  WHEN MATCHED THEN  \r\n" + 
    			"   update set a.recv_num=b.recv_num,UPDATE_TIME = sysdate \r\n" + 
    			"  WHEN not MATCHED THEN    \r\n" + 
    			"  insert (STATIS_DATE,STATIS_TYPE,STATIS_KIND,RECV_NUM,INESRT_TIME,UPDATE_TIME) \r\n" + 
    			"  values (b.STATIS_DATE,b.STATIS_TYPE,b.STATIS_KIND,b.RECV_NUM,sysdate,sysdate)  ";
    	 Integer count = DBUtils.execute(conn, sql);
 		 XxlJobLogger.log("[普通] 门架cpc交易量数据(条):" + count);	   
    }
    
}
